﻿using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Data.Sqlite;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using KorbeRTR.Utility;

namespace KorbeRTR.Models
{
    /// <summary>
    /// 料号
    /// </summary>
    [Table(Name = "Recipes")]
    public class RecipeView
    {
        /// <summary>
        /// 料号ID
        /// </summary>
        [Column]
        public Int64 Id { get; set; }

        /// <summary>
        /// 料号名
        /// </summary>
        [Column]
        public string Name { get; set; }

        /// <summary>
        /// 软删除标志
        /// </summary>
        [Column]
        public bool IsDeleted { get; set; }

        /// <summary>
        /// 备注
        /// </summary>
        [Column]
        public string Comment { get; set; }

        /// <summary>
        /// 建立用户
        /// </summary>
        [Column]
        public string User { get; set; }

        /// <summary>
        /// 建立时间
        /// </summary>
        [Column]
        public DateTime Time { get; set; }


        /// <summary>
        /// 宽度(mm)
        /// </summary>
        [Column]
        public double Width { get; set; }

        /// <summary>
        /// 宽度(dm)
        /// </summary>
        public double WidthDm => Width / 100;

        /// <summary>
        /// 宽度(inch)
        /// </summary>
        public double WidthInch => Width / 25.4;


        /// <summary>
        /// 电流效率(%)
        /// </summary>
        [Column(Name = "DLXL")]
        public double D电流效率 { get; set; }

        /// <summary>
        /// 板厚(mm)
        /// </summary>
        [Column]
        public double Thickness { get; set; }

        /// <summary>
        /// 镀铜C面厚度(um)
        /// </summary>
        [Column(Name = "DTCMHD")]
        public double D镀铜C面厚度 { get; set; }

        /// <summary>
        /// 镀铜S面厚度(um)
        /// </summary>
        [Column(Name = "DTSMHD")]
        public double D镀铜S面厚度 { get; set; }

        /// <summary>
        /// 镀铜时间(min)
        /// </summary>
        [Column(Name = "DTSJ")]
        public double D镀铜时间 { get; set; }

        /// <summary>
        /// 镀铜速度(mm/s)
        /// </summary>
        [Column(Name = "DTSD")]
        public double D镀铜速度 { get; set; }


        /// <summary>
        /// 镀铜速度(m/min)
        /// </summary>
        public double D镀铜速度mpmin
        {
            get
            {
                return D镀铜速度 * 60 / 1000;
            }
            set
            {
                D镀铜速度 = value * 1000 / 60;
            }
        }

        /// <summary>
        /// C面电流密度(ASD)
        /// </summary>
        [Column(Name = "CMDLMD")]
        public double C面电流密度ASD { get; set; }

        /// <summary>
        /// C面电流密度(ASF)
        /// </summary>
        public double C面电流密度ASF
        {
            get
            {
                return C面电流密度ASD * 9.290304;
            }
            set
            {
                C面电流密度ASD = value / 9.290304;
            }
        }

        /// <summary>
        /// S面电流密度(ASD)
        /// </summary>
        [Column(Name = "SMDLMD")]
        public double S面电流密度ASD { get; set; }

        /// <summary>
        /// C面电流密度(ASF)
        /// </summary>
        public double S面电流密度ASF
        {
            get
            {
                return S面电流密度ASD * 9.290304;
            }
            set
            {
                S面电流密度ASD = value / 9.290304;
            }
        }

        /// <summary>
        /// 卡板报警电压(V)
        /// </summary>
        [Column(Name = "KBBJDY")]
        public double K卡板报警电压 { get; set; }

        /// <summary>
        /// 除油频率(Hz)
        /// </summary>
        [Column(Name = "CY1PL")]
        public double C除油1频率 { get; set; }

        /// <summary>
        /// 除油频率(Hz)
        /// </summary>
        [Column(Name = "CY2PL")]
        public double C除油2频率 { get; set; }

        public CuSlotRecipe[] CuSlots { get; }

        public RecipeView()
        {
            CuSlots = new CuSlotRecipe[KorbeRTR.Config.镀铜段数];
            for (int i = 0; i < CuSlots.Length; i++)
            {
                CuSlots[i] = new CuSlotRecipe()
                {
                    Owner = this,
                    OwnerId = this.Id,
                    Slot = i + 1,
                };
            }
        }

        public static Int64 Add(string recipes, string cu_slot_recipes, RecipeView view)
        {
            var dt = RecipesDBHelper.ExecuteTable(
                string.Format(@"INSERT INTO {0}(Name,Comment,User,Time,Width,Thickness,DLXL,DTCMHD,DTSMHD,DTSJ,DTSD,CMDLMD,SMDLMD,KBBJDY,CY1PL,CY2PL)
                  VALUES(@Name,@Comment,@User,@Time,@Width,@Thickness,@DLXL,@DTCMHD,@DTSMHD,@DTSJ,@DTSD,@CMDLMD,@SMDLMD,@KBBJDY,@CY1PL,@CY2PL);
                  SELECT LAST_INSERT_ROWID();", recipes),
                new SqliteParameter("@Name", view.Name),
                new SqliteParameter("@Comment", view.Comment),
                new SqliteParameter("@User", view.User),
                new SqliteParameter("@Time", view.Time.ToSqlTime()),
                new SqliteParameter("@Width", view.Width),
                new SqliteParameter("@Thickness", view.Thickness),
                new SqliteParameter("@DLXL", view.D电流效率),
                new SqliteParameter("@DTCMHD", view.D镀铜C面厚度),
                new SqliteParameter("@DTSMHD", view.D镀铜S面厚度),
                new SqliteParameter("@DTSJ", view.D镀铜时间),
                new SqliteParameter("@DTSD", view.D镀铜速度),
                new SqliteParameter("@CMDLMD", view.C面电流密度ASD),
                new SqliteParameter("@SMDLMD", view.S面电流密度ASD),
                new SqliteParameter("@KBBJDY", view.K卡板报警电压),
                new SqliteParameter("@CY1PL", view.C除油1频率),
                new SqliteParameter("@CY2PL", view.C除油2频率)
                );

            view.Id = (Int64)dt.Rows[0][0];
            for (int i = 0; i < view.CuSlots.Length; i++)
            {
                var slot = view.CuSlots[i];
                slot.OwnerId = slot.Owner.Id;
            }

            using (var conn = RecipesDBHelper.Open())
            {
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        for (int i = 0; i < view.CuSlots.Length; i++)
                        {
                            var slot = view.CuSlots[i];

                            CurrentRecordDBHelper.ExecuteNonQuery(trans,
                                string.Format(@"INSERT INTO {0}(Slot,OwnerId,DLXS,Flow)
                                    VALUES(@Slot,@OwnerId,@DLXS,@Flow)", cu_slot_recipes),
                                new SqliteParameter("@Slot", slot.Slot),
                                new SqliteParameter("@OwnerId", slot.OwnerId),
                                new SqliteParameter("@DLXS", slot.D电流系数),
                                new SqliteParameter("@Flow", slot.Flow)
                                );
                        }

                        trans.Commit();
                    }
                    catch (Exception err)
                    {
                        trans.Rollback();
                        throw err;
                    }
                }
            }

            return view.Id;
        }

        /// <summary>
        /// 添加料号
        /// </summary>
        /// <returns></returns>
        public static bool Add(RecipeView view)
        {
            var id = Add("Recipes", "CuSlotRecipes", view);
            return true;
        }

        /// <summary>
        /// 删除料号
        /// </summary>
        public static bool Delete(RecipeView view)
        {
            var rows = RecipesDBHelper.ExecuteNonQuery(
                @"UPDATE Recipes SET IsDeleted=TRUE WHERE Id=@Id",
                new SqliteParameter("@Id", view.Id));
            return rows > 0;
        }

        /// <summary>
        /// 更新料号
        /// </summary>
        public static bool Update(RecipeView view)
        {
            var rows = RecipesDBHelper.ExecuteNonQuery(
                @"UPDATE Recipes SET Name=@Name,Comment=@Comment,User=@User,Time=@Time,Width=@Width,Thickness=@Thickness,
                    DLXL=@DLXL,DTCMHD=@DTCMHD,DTSMHD=@DTSMHD,
                    DTSJ=@DTSJ,DTSD=@DTSD,CMDLMD=@CMDLMD,SMDLMD=@SMDLMD,KBBJDY=@KBBJDY,CY1PL=@CY1PL,CY2PL=@CY2PL WHERE Id=@Id",
                new SqliteParameter("@Name", view.Name),
                new SqliteParameter("@Comment", view.Comment),
                new SqliteParameter("@User", view.User),
                new SqliteParameter("@Time", view.Time),
                new SqliteParameter("@Width", view.Width),
                new SqliteParameter("@Thickness", view.Thickness),
                new SqliteParameter("@DLXL", view.D电流效率),
                new SqliteParameter("@DTCMHD", view.D镀铜C面厚度),
                new SqliteParameter("@DTSMHD", view.D镀铜S面厚度),
                new SqliteParameter("@DTSJ", view.D镀铜时间),
                new SqliteParameter("@DTSD", view.D镀铜速度),
                new SqliteParameter("@CMDLMD", view.C面电流密度ASD),
                new SqliteParameter("@SMDLMD", view.S面电流密度ASD),
                new SqliteParameter("@KBBJDY", view.K卡板报警电压),
                new SqliteParameter("@CY1PL", view.C除油1频率),
                new SqliteParameter("@CY2PL", view.C除油2频率),
                new SqliteParameter("@Id", view.Id)
                );

            using (var conn = RecipesDBHelper.Open())
            {
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        for (int i = 0; i < view.CuSlots.Length; i++)
                        {
                            var slot = view.CuSlots[i];

                            CurrentRecordDBHelper.ExecuteNonQuery(trans,
                                @"UPDATE CuSlotRecipes SET Slot=@Slot,OwnerId=@OwnerId,DLXS=@DLXS,Flow=@Flow WHERE Id=@Id",
                                new SqliteParameter("@Slot", slot.Slot),
                                new SqliteParameter("@OwnerId", slot.OwnerId),
                                new SqliteParameter("@DLXS", slot.D电流系数),
                                new SqliteParameter("@Flow", slot.Flow),
                                new SqliteParameter("@Id", slot.Id)
                                );
                        }

                        trans.Commit();
                    }
                    catch (Exception err)
                    {
                        trans.Rollback();
                        throw err;
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// 列出全部配方
        /// </summary>
        public static Dictionary<Int64, RecipeView> ListAll(string recipes, string cu_slot_recipes)
        {
            var dict = new Dictionary<Int64, RecipeView>();
            {   // 读取配方参数
                var dt = RecipesDBHelper.ExecuteTable(string.Format("SELECT * FROM {0} WHERE IsDeleted=FALSE", recipes));
                foreach(var iter in dt.ToModel<RecipeView>())
                {
                    dict[iter.Id] = iter;
                }
            }

            {   // 读取镀铜槽参数
                var dt = RecipesDBHelper.ExecuteTable(string.Format("SELECT * FROM {0}", cu_slot_recipes));
                var list = dt.ToModel<CuSlotRecipe>();

                // 配方参数和镀铜槽参数匹配
                foreach (var slot in list)
                {
                    if (dict.ContainsKey(slot.OwnerId))
                    {
                        var owner = dict[slot.OwnerId];
                        if (0 < slot.Slot && slot.Slot <= owner.CuSlots.Length)
                        {
                            owner.CuSlots[slot.Slot - 1] = slot;
                            slot.Owner = owner;
                        }
                    }
                }
            }

            return dict;
        }

        /// <summary>
        /// 列出全部配方
        /// </summary>
        public static List<RecipeView> ListAll()
        {
            return ListAll("Recipes", "CuSlotRecipes").Values.ToList();
        }

        public static RecipeView SelectById(long id)
        {
            string tb_recipe = "Recipes";
            string tb_cu_slot_recipes = "CuSlotRecipes";

            var dict = new Dictionary<Int64, RecipeView>();
            {   // 读取配方参数
                var dt = RecipesDBHelper.ExecuteTable(string.Format("SELECT * FROM {0} WHERE IsDeleted=FALSE AND Id=@Id", tb_recipe),
                    new SqliteParameter("@Id", id));
                foreach (var dm in dt.ToModel<RecipeView>())
                {
                    dict[dm.Id] = dm;
                }
            }

            {   // 读取镀铜槽参数
                var dt = RecipesDBHelper.ExecuteTable(string.Format("SELECT * FROM {0} WHERE OwnerId=@OwnerId", tb_cu_slot_recipes),
                    new SqliteParameter("@OwnerId", id));
                var list = dt.ToModel<CuSlotRecipe>();

                // 配方参数和镀铜槽参数匹配
                foreach (var slot in list)
                {
                    if (dict.ContainsKey(slot.OwnerId))
                    {
                        var owner = dict[slot.OwnerId];
                        if (0 < slot.Slot && slot.Slot <= owner.CuSlots.Length)
                        {
                            owner.CuSlots[slot.Slot - 1] = slot;
                            slot.Owner = owner;
                        }
                    }
                }
            }

            if (dict.Count > 0)
                return dict.Values.First();
            else
                return null;
        }

        public static List<RecipeView> FilterByName(string name)
        {
            string tb_recipe = "Recipes";
            string tb_cu_slot_recipes = "CuSlotRecipes";

            var dict = new Dictionary<Int64, RecipeView>();
            {   // 读取配方参数
                var dt = RecipesDBHelper.ExecuteTable(string.Format("SELECT * FROM {0} WHERE IsDeleted=FALSE AND Name like @Name", tb_recipe),
                    new SqliteParameter("@Name", "%" + name + "%"));
                foreach (var dm in dt.ToModel<RecipeView>())
                {
                    dict[dm.Id] = dm;
                }
            }

            {   // 读取镀铜槽参数
                var dt = RecipesDBHelper.ExecuteTable(string.Format("SELECT * FROM {0}", tb_cu_slot_recipes));
                var list = dt.ToModel<CuSlotRecipe>();

                // 配方参数和镀铜槽参数匹配
                foreach (var slot in list)
                {
                    if (dict.ContainsKey(slot.OwnerId))
                    {
                        var owner = dict[slot.OwnerId];
                        if (0 < slot.Slot && slot.Slot <= owner.CuSlots.Length)
                        {
                            owner.CuSlots[slot.Slot - 1] = slot;
                            slot.Owner = owner;
                        }
                    }
                }
            }

            return dict.Values.ToList();
        }

        public static RecipeView SelectByNameLike(string name)
        {
            string tb_recipe = "Recipes";
            string tb_cu_slot_recipes = "CuSlotRecipes";

            var dict = new Dictionary<Int64, RecipeView>();
            {   // 读取配方参数
                var dt = RecipesDBHelper.ExecuteTable(string.Format("SELECT * FROM {0} WHERE IsDeleted=FALSE AND Name like @Name", tb_recipe),
                    new SqliteParameter("@Name", "%" + name + "%"));
                foreach (var dm in dt.ToModel<RecipeView>())
                {
                    dict[dm.Id] = dm;
                }
            }

            {   // 读取镀铜槽参数
                var dt = RecipesDBHelper.ExecuteTable(string.Format("SELECT * FROM {0}", tb_cu_slot_recipes));
                var list = dt.ToModel<CuSlotRecipe>();

                // 配方参数和镀铜槽参数匹配
                foreach (var slot in list)
                {
                    if (dict.ContainsKey(slot.OwnerId))
                    {
                        var owner = dict[slot.OwnerId];
                        if (0 < slot.Slot && slot.Slot <= owner.CuSlots.Length)
                        {
                            owner.CuSlots[slot.Slot - 1] = slot;
                            slot.Owner = owner;
                        }
                    }
                }
            }

            if (dict.Count > 0)
                return dict.Values.First();
            else
                return null;
        }
    }

    /// <summary>
    /// 镀铜槽工艺参数
    /// </summary>
    [Table(Name = "CuSlotRecipes")]
    public class CuSlotRecipe
    {
        /// <summary>
        /// 料号ID
        /// </summary>
        [Column]
        public Int64 Id { get; set; }

        /// <summary>
        /// 镀铜槽号
        /// </summary>
        [Column]
        public Int64 Slot { get; set; }

        /// <summary>
        /// 拥有者
        /// </summary>
        [Column]
        public Int64 OwnerId { get; set; }

        /// <summary>
        /// 软删除标志
        /// </summary>
        [Column]
        public bool IsDeleted { get; set; }


        /// <summary>
        /// D电流系数
        /// </summary>
        [Column(Name = "DLXS")]
        public double D电流系数 { get; set; }

        [Column]
        public double Flow { get; set; }

        public double 流量
        {
            get
            {
                return Flow;
            }
            set
            {
                Flow = value;
            }
        }

        /// <summary>
        /// C面电流(A)
        /// </summary>
        public double C面电流 => 10 * Owner.WidthDm * Owner.C面电流密度ASD * D电流系数;

        /// <summary>
        /// S面电流(A)
        /// </summary>
        public double S面电流 => 10 * Owner.WidthDm * Owner.S面电流密度ASD * D电流系数;

        /// <summary>
        /// 所属料号
        /// </summary>
        public RecipeView Owner { get; set; }
    }

}
